import java.sql.*;

import javax.sql.*;

import java.io.IOException;

import java.lang.*;
import java.util.*;

public class DB 
{
	private String url = "jdbc:mysql://localhost:3306/";
	private String dbName = "USTC_Hucat_DB";
	private String driverName = "com.mysql.jdbc.Driver";
	private String userName = "whu4";
	private String passwd = "yz62658818";
	
	private HashMap<String, String> tableList = null;
	
	private Connection conn = null;
	private Statement st = null;
	
	DB()
	{
		tableList = new HashMap<String, String>();
		
		tableList.put("bugContent", "(" +
				"bugid INT, " +
				"title VARCHAR(1000), " +
				"status VARCHAR(100), " +
				"owner VARCHAR(100), " +
				"closedOn VARCHAR(100), " +
				"type VARCHAR(100), " +
				"priority VARCHAR(100), " +
				"component VARCHAR(100), " +
				"stars INT, " +
				"reportedBy VARCHAR(100), " +
				"openedDate VARCHAR(100), " +
				"description LONGTEXT, " +
				//"PRIMARY KEY(bugid), " +
				"UNIQUE(bugid)"
				+ ")");
		
		tableList.put("bugComment", "(" +
				"bugid INT, " +
				"author VARCHAR(100), " +
				"whe VARCHAR(100), " +
				"wha text"
				//"PRIMARY KEY(bugid)"
				+ ")");
	}
	
	DB(HashMap<String, String> tableList)
	{
		tableList = tableList;
	}
	
	//open connection
	public void connect() throws ClassNotFoundException, IllegalAccessException, 
		SQLException 
	{
		try
		{
			Class.forName(driverName).newInstance();
			conn = DriverManager.getConnection(url + dbName, userName, passwd);
			System.out.println("Connected to the database");
			
			st = conn.createStatement();
						
			//st.executeUpdate("DROP DATABASE if EXISTS " + dbName);
			//st.executeUpdate("CREATE DATABASE " + dbName);
			st.executeUpdate("USE " + dbName);		
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	//close connection
	public void close() throws SQLException
	{
		try
		{
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		System.out.println("Disconnected from database");
	}
	
	//build the table given the name and the schema
	public void buildTable(String tableName, String tableSchema) throws Exception
	{
		try
		{
			st.executeUpdate("CREATE TABLE if NOT EXISTS " + tableName + tableSchema);
			//System.out.println("CREATE TABLE if NOT EXISTS " + tableName + tableSchema);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		System.out.println("Build table --> " + tableName);
	}
	
	//build all the table
	public void buildAll() throws Exception
	{
		Set<String> tableNameSet = tableList.keySet();
		
		for (String tableName: tableNameSet)
		{
			dropTable(tableName);
			
			String tableSchema = tableList.get(tableName);
			
			buildTable(tableName, tableSchema);
		}
	}
	
	//drop table gieven the name
	public void dropTable(String tableName)
	{
		try
		{
			st.executeUpdate("DROP TABLE if EXISTS " + tableName);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		System.out.println("Drop table --> " + tableName);
	}
	
	//drop all the table
	public void dropAll() throws Exception
	{
		Set<String> tableNameSet = tableList.keySet();
		
		for (String tableName: tableNameSet)
		{
			dropTable(tableName);
			
			String tableSchema = tableList.get(tableName);
			
			dropTable(tableName);
		}
	}
	
	public void insertTable(String tableName, String insertStatement) throws SQLException
	{
		String statement = new String("INSERT into ");
		statement += tableName;
		statement += " ";
		
		statement += insertStatement;
		
		//statement = statement.replace('\'', '1');
		
		//System.out.println(statement);
		
		try
		{
			st.executeUpdate(statement);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("hucat");
			System.out.println(statement);
		}
	}
	
	static public void main(String argx[]) throws Exception
	{	
		//System.out.println(tableList.get("bugContent"));
		//System.out.println(tableList.get("bugComment"));
		
		DB db = new DB();
		
		db.connect();
		db.buildAll();
		db.dropAll();
		db.close();
	}
}
